<?php

namespace App;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use DB;


class Order extends Authenticatable
{
    use Notifiable;
	protected $table = 'order';

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
         'remember_token',
    ];

    //订单管理列表
    public static function getAllOrderList($order_no = '',$shopId = '',$state='',$serveS=''){

	    $sql = "SELECT a.id as oid,a.order_no,a.car_code,b.phone,c.id as sid,c.shopName,d.serveName,a.actual_money,a.order_money,e.name as couponName,a.state,a.server_type FROM c_order AS a
			LEFT JOIN c_user_account AS b ON a.open_id=b.open_id
			LEFT JOIN c_bank_show AS c ON a.bank_id=c.id
			LEFT JOIN c_serve_projects AS d ON a.server_id=d.id
			LEFT JOIN c_coupon AS e ON a.coupon_id=e.id WHERE 1=1 ";

		if($order_no){
			$sql.=" AND ( a.order_no LIKE '%$order_no%' or a.car_code LIKE '%$order_no%' )";
		}
		if($shopId){
			$sql.=' AND c.id='.$shopId;
		}
		if($state){
			$sql.=' AND a.state='.$state;
		}
	    if($serveS){
		    $sql.=' AND a.server_type='.$serveS;
	    }

	    $orders = DB::select($sql);

	    /*$orders = DB::table('order as a')
		    ->leftjoin('user_account as b', 'a.open_id', '=', 'b.open_id')
		    ->leftjoin('bank_show as c', 'a.bank_id', '=', 'c.id')
		    ->leftjoin('serve_projects as d', 'a.server_id', '=', 'd.id')
		    ->leftjoin('coupon as e', 'a.coupon_id', '=', 'e.id')
		    ->select('a.id','a.order_no','a.car_code', 'b.phone','c.shopName','d.serveName','a.actual_money','a.order_money','e.name as couponName','a.state')
//		    ->toSql();
		    ->get()
		    ->toArray();*/
	    if(!$orders){
		    return array();
	    }
	    return $orders;
    }

    // 获取指定订单详情
	public static function getOrderInfo($id){
		$orderInfo = DB::table('order as a')
			->leftjoin('user_account as b', 'a.open_id', '=', 'b.open_id')
			->leftjoin('bank_show as c', 'a.bank_id', '=', 'c.id')
			->leftjoin('coupon as e', 'a.coupon_id', '=', 'e.id')
			->where('a.id', $id)
			->select('a.id','a.order_no','a.create_date','b.phone','b.nickname','c.shopName','a.actual_money','a.order_money','e.name as couponName','a.state','a.number_times')
//		    ->toSql();
			->get()
			->toArray();
		if(!$orderInfo){
			return array();
		}
		return $orderInfo[0];
	}

    // 获取订单对应的服务
	public static function getOrderServe($orderId){
		$orderServe = DB::table('order as a')
			->leftjoin('serve_projects as d', 'a.server_id', '=', 'd.id')
			->where('a.id',$orderId)
			->select('a.car_code','d.*')
			->get()
			->toArray();
		if(!$orderServe){
			return array();
		}
		return $orderServe[0];
	}

	// 获取订单使用次数
	public static function getOrderUseHistory($orderId){
		$orderUse = DB::table('use_message as a')
			->leftjoin('bank_show as d', 'a.bank_id', '=', 'd.id')
			->where('a.order_id',$orderId)
			->select('a.*','d.shopName')
			->get()
			->toArray();
		if(!$orderUse){
			return array();
		}
		return $orderUse;
	}



	// 获取退款订单原因
	public static function getOrderFund($orderId){
		$orderRe = DB::table('order as a')
			->leftjoin('order_refund as b', 'a.id', '=', 'b.order_id')
			->where('a.id',$orderId)
			->select('b.check_text','b.desc_text','b.create_date')
			->get()
			->toArray();
		if(!$orderRe){
			return array();
		}
		return $orderRe[0];
	}

	// 日订单量统计
	public static function getOrdersEverydayTotal($where,$bank_id='',$e_codeS=''){
		/*$sql = "
		SELECT su.* FROM
		(
			SELECT a.times,a.xdTotal,a.xdTotalMoney,b.thTotal,b.thTotalMoney,a.bank_id FROM (
			SELECT LEFT(create_date,10) AS times ,COUNT(id) AS xdTotal ,SUM(order_money) AS xdTotalMoney,bank_id FROM c_order   
					WHERE (state<=4 AND state>=1 )
					GROUP BY times,bank_id 
			)AS a 
			LEFT JOIN (
				SELECT LEFT(r.create_date,10)AS times,COUNT(r.id) AS thTotal ,SUM(i.order_money) AS thTotalMoney  FROM c_order AS i
				INNER JOIN c_order_refund AS r ON i.id=r.order_id 
				WHERE (i.state=6 )
				GROUP BY times 
			) AS b ON a.times=b.times 
		) AS su WHERE 1=1
		";*/
		$sql = "
		SELECT su.* FROM
		(
		SELECT a.times,a.xdTotal,a.xdTotalMoney,b.thTotal,b.thTotalMoney,a.bank_id,a.e_code FROM (
			SELECT LEFT(create_date,10) AS times ,COUNT(id) AS xdTotal ,SUM(order_money) AS xdTotalMoney,bank_id,e_code FROM c_order   
					WHERE (state<=4 AND state>1 ) 
					GROUP BY times,bank_id,e_code
			)AS a 
			LEFT JOIN (
				SELECT LEFT(r.create_date,10)AS times,COUNT(r.id) AS thTotal ,SUM(i.order_money) AS thTotalMoney,bank_id,e_code FROM c_order AS i
				INNER JOIN c_order_refund AS r ON i.id=r.order_id 
				WHERE i.state=6  
				GROUP BY times,bank_id,e_code 
		       ) AS b ON a.times=b.times			
		) AS su WHERE 1=1
		";


		if($where){
			$sql.=" AND times >='".$where['startTime']."' AND times <='".$where['endTime']."'";
		}

		if($bank_id){
			$sql.=" AND bank_id=".$bank_id;
		}

		if($e_codeS){
			$sql.=" AND e_code like '%$e_codeS%' ";
		}

		$orders = DB::select($sql);
		if(!$orders){
			return array();
		}
		return $orders;

	}


	// 财务数据统计
	public static function getOrdersMoneyTotal($where,$bank_id){
//		$sql = "
//		SELECT su.* FROM
//		(
//			SELECT a.times,a.yfMoney,a.sfMoney,b.dcTotalMoney FROM
//				(
//				SELECT LEFT(create_date,10) AS times ,SUM(actual_money) AS yfMoney,SUM(order_money) AS sfMoney FROM c_order WHERE state >1 AND state<=4 GROUP BY times
//				) AS a
//				LEFT JOIN
//				(
//				SELECT LEFT(create_date,10) AS times,SUM(dc.money) AS dcTotalMoney FROM c_order  AS i
//				INNER JOIN  c_coupon  AS dc WHERE i.coupon_id=dc.id AND i.state >1 AND i.state<=4
//				GROUP BY times
//				) AS b ON a.times=b.times
//	) AS su WHERE 1=1 ";

		$sql = "
		SELECT su.* FROM
		(
			SELECT a.bank_id,a.times,a.yfMoney,a.sfMoney,b.dcTotalMoney FROM 
				(
				SELECT bank_id,LEFT(create_date,10) AS times ,SUM(actual_money) AS yfMoney,SUM(order_money) AS sfMoney FROM c_order WHERE state >1 AND state<=4 GROUP BY times,bank_id
				) AS a 
				LEFT JOIN 
				(
				SELECT bank_id,LEFT(create_date,10) AS times,SUM(dc.money) AS dcTotalMoney FROM c_order  AS i
				INNER JOIN  c_coupon  AS dc WHERE i.coupon_id=dc.id AND i.state >1 AND i.state<=4 
				GROUP BY times, bank_id 
				) AS b ON a.times=b.times AND a.bank_id=b.bank_id
	) AS su WHERE 1=1
		";

		if($where){
			$sql.=" AND times >='".$where['startTime']."' AND times <='".$where['endTime']."'";
		}

		if($bank_id){
			$sql.=" AND bank_id=".$bank_id;
		}

		$orders = DB::select($sql);
		if(!$orders){
			return array();
		}
		return $orders;

	}

	// 根据网点、设备 统计订单总数及金额
	public static  function getOrderBankShowEcodeTotal($e_codeS='',$shopS=''){
		$data = array();
		$sql_xd = " SELECT COUNT(*) AS xdTotal,SUM(order_money) as xdMoney FROM c_order WHERE state<=4 AND state>1 ";

		$sql_th = " SELECT COUNT(*) AS thTotal,SUM(order_money) as thMoney FROM c_order WHERE state<=6 AND state>=5 ";

		if($e_codeS){
			$sql_xd.=" AND e_code like '%$e_codeS%' ";
			$sql_th.=" AND e_code like '%$e_codeS%' ";
		}

		if($shopS){
			$sql_xd.=" AND bank_id=".$shopS;
			$sql_th.=" AND bank_id=".$shopS;
		}

		$order_xd = DB::select($sql_xd);
		if(!$order_xd){
			$order_xd =  array();
		}

		$order_th = DB::select($sql_th);
		if(!$order_th){
			$order_th =  array();
		}

		$data['order_xd'] = $order_xd;
		$data['order_th'] = $order_th;
		return $data;

	}


}
